<?php
/**
 *  数据库类
 *
 * @copyright			(C) 2011-11-11 CLOUD
 * @license				
 * 
 */

class Db_Mysql
{
	private $link = null;
	
	private $dbconfig = array();
	
	private $errormsg = '';
	
	private $querycount = 0;
	
	private static $object;
	
	/**
	 * 最近一次查询资源句柄
	 */
	public $lastqueryid = null;
	
	
	private function __construct()
	{
		$this->dbconfig = Common::getConfig( 'Database' );
		$this->dbconfig['dbport'] = empty( $this->dbconfig['dbport'] ) ? 3306 : $this->dbconfig['dbport'];
	}
	
	public static function getInstance()
	{
		if ( !is_object( self::$object ) )
		{
			self::$object = new Db_Mysql();
		}
		return self::$object;
	}
	
	/**
	 * 数据库连接
	 */
	private function connect()
	{
		if ( !$this->link = mysql_connect( $this->dbconfig['dbhost'] . ':' . $this->dbconfig['dbport'] , $this->dbconfig['dbuser'] , $this->dbconfig['dbpass'] ) )
		{
			$this->halt( 'Can not connect to MySQL server!' );
			return false;
		}
		if ( $this->version() > '4.1' )
		{
			$charset = $this->dbconfig['dbcharset'] ? $this->dbconfig['dbcharset'] : '';
			$serverCharsetStr = $charset ? "character_set_connection='$charset',character_set_results='$charset',character_set_client=binary" : '';
			$serverCharsetStr .= $this->version() > '5.0.1' ? ( ( empty( $serverCharsetStr ) ? '' : ',' ) . " sql_mode='' ") : '';
			$serverCharsetStr && mysql_query( "SET $serverCharsetStr" , $this->link );
		}
		
		if( $this->dbconfig['dbname'] && !@mysql_select_db( $this->dbconfig['dbname'] , $this->link ) ) 
		{
			$this->halt('Cannot use database '.$this->dbconfig['dbname']);
			return false;
		}
	}
	
/**
	 * 数据库查询执行方法
	 * @param string $sql 要执行的sql语句
	 * @return 查询资源句柄
	 */
	public function query( $sql ) 
	{
		if( !is_resource( $this->link ) ) 
		{
			$this->connect();
		}
		
		$this->lastqueryid = mysql_query( $sql, $this->link ) or $this->halt( mysql_error() , $sql );

		$this->querycount++;
		return $this->lastqueryid;
	}

	/**
	 * 执行sql查询
	 * @param string $data 			需要查询的字段值,[`name`,`gender`]
	 * @param string $table 		数据表
	 * @param string $where 		查询条件，[`name`='$name']
	 * @param string $limit 		返回结果范围，[10或10,10 默认为空]
	 * @param string $order 		排序方式	[默认按数据库默认方式排序]
	 * @param string $group 		分组方式	[默认为空]
	 * @param string $key 			返回数组按键名排序
	 * @return array				查询结果集数组
	 */
	public function select( $data , $table , $where = '' , $limit = '' , $order = '' , $group = '' , $key = '' ) 
	{
		if( !is_resource( $this->link ) ) 
		{
			$this->connect();
		}
		
		$where = $where == '' ? '' : ' WHERE ' . $where;
		$order = $order == '' ? '' : ' ORDER BY ' . $order;
		$group = $group == '' ? '' : ' GROUP BY ' . $group;
		$limit = $limit == '' ? '' : ' LIMIT ' . $limit;
		$field = explode( ',' , $data );
		array_walk( $field, array( $this , 'addSpecialChar' ) );
		$data = implode( ',' , $field );

		$sql = 'SELECT ' . $data . ' FROM `' . $this->dbconfig['dbname'] . '` . `' . $table . '`' . $where . $group . $order . $limit;
		$this->query( $sql );
		if( !is_resource( $this->lastqueryid ) ) 
		{
			return $this->lastqueryid;
		}

		$datalist = array();
		while( ( $rs = $this->fetchNext() ) != false ) 
		{
			if( $key ) 
			{
				$datalist[$rs[$key]] = $rs;
			} 
			else 
			{
				$datalist[] = $rs;
			}
		}
		$this->freeResult();
		return $datalist;
	}
	
	/**
	 * 获取单表的数据总量
	 * @param string $table 		数据表
	 * @param string $where 		查询条件
	 * @param string $order 		排序方式	[默认按数据库默认方式排序]
	 * @param string $group 		分组方式	[默认为空]
	 * @return array/null	数据查询结果集,如果不存在，则返回空
	 */
	public function getCount( $table , $where = '' , $order = '' , $group = '' )
	{
		$res = $this->getOne( 'COUNT(*) AS num' , $table , $where = '' , $order = '' , $group = '' );
		return $res['num'];
	}

	/**
	 * 获取单条记录查询
	 * @param string $data 		需要查询的字段值[`name`,`gender`]
	 * @param string $table 		数据表
	 * @param string $where 		查询条件
	 * @param string $order 		排序方式	[默认按数据库默认方式排序]
	 * @param string $group 		分组方式	[默认为空]
	 * @return array/null	数据查询结果集,如果不存在，则返回空
	 */
	public function getOne( $data , $table , $where = '' , $order = '' , $group = '' ) 
	{
		$where = $where == '' ? '' : ' WHERE ' . $where;
		$order = $order == '' ? '' : ' ORDER BY ' . $order;
		$group = $group == '' ? '' : ' GROUP BY ' . $group;
		$limit = ' LIMIT 1';
		$field = explode( ',' , $data );
		array_walk( $field , array( $this , 'addSpecialChar' ) );
		$data = implode( ',' , $field );

		$sql = 'SELECT ' . $data . ' FROM `' . $this->dbconfig['dbname'] . '` . `' . $table . '`' . $where . $group . $order . $limit;
		$this->query( $sql );
		$res = $this->fetchNext();
		$this->freeResult();
		return $res;
	}
	
	/**
	 * 根据sql获取一条数据
	 * @param unknown_type $sql
	 */
	public function getOneBySql( $sql )
	{
		$this->query( $sql );
		$res = $this->fetchNext();
		$this->freeResult();
		return $res;
	}
	
	public function createTableSql( $table )
	{
		$sql = '';
		$sql .= "DROP TABLE IF EXISTS `".$table."`;\r\n";
		$row = $this->getOneBySql( "SHOW CREATE TABLE " . $table );
		$sql .= $row['Create Table'];
		return $sql;
	}
	
	/**
	 * 遍历查询结果集
	 * @param $type		返回结果集类型	
	 * 					MYSQL_ASSOC，MYSQL_NUM 和 MYSQL_BOTH
	 * @return array
	 */
	public function fetchNext( $type = MYSQL_ASSOC ) 
	{
		$res = mysql_fetch_array( $this->lastqueryid , $type );
		if(!$res) 
		{
			$this->freeResult();
		}
		return $res;
	}
	
	/**
	 * 释放查询资源
	 * @return void
	 */
	public function freeResult() 
	{
		if( is_resource( $this->lastqueryid ) ) 
		{
			mysql_free_result( $this->lastqueryid );
			$this->lastqueryid = null;
		}
	}
	
	/**
	 * 执行添加记录操作
	 * @param	array $data 	要增加的数据，参数为数组。数组key为字段值，数组值为数据取值
	 * @param	string $table 	数据表
	 * @param	bool $returnInsertId	是否返回插入记录ID
	 * @param	bool $replace	是否使用REPLACE INTO
	 * @return boolean
	 */
	public function insert( $data , $table , $returnInsertId = false , $isReplace = false ) 
	{
		if(!is_array( $data ) || $table == '' || count($data) == 0) 
		{
			return false;
		}
		
		$fielddata = array_keys($data);
		$valuedata = array_values($data);
		array_walk( $fielddata , array( $this , 'addSpecialChar' ) );
		array_walk( $valuedata , array( $this , 'escapeString' ) );
		$field = implode ( ',' , $fielddata );
		$value = implode ( ',' , $valuedata );

		$cmd = $isReplace ? 'REPLACE INTO' : 'INSERT INTO';
		$sql = $cmd . ' `' . $this->dbconfig['dbname'] . '` . `' . $table . '`(' . $field . ') VALUES (' . $value . ')';
		$return = $this->query( $sql );
		return $returnInsertId ? $this->insertId() : $return;
	}
	
	/**
	 * 获取最后一次添加记录的主键号
	 * @return int 
	 */
	public function insertId() 
	{
		return mysql_insert_id( $this->link );
	}
	
	/**
	 * 执行更新记录操作
	 * @param $data 				要更新的数据内容，参数可以为数组也可以为字符串，建议数组。
	 * 								为数组时数组key为字段值，数组值为数据取值
	 * 								为字符串时[例：`name`='phpcms',`hits`=`hits`+1]。
	 *								为数组时[例: array('name'=>'phpcms','password'=>'123456')]
	 *								数组可使用array('name'=>'+=1', 'base'=>'-=1');程序会自动解析为`name` = `name` + 1, `base` = `base` - 1
	 * @param	string $table 		数据表
	 * @param	string $where 		更新数据时的条件
	 * @return boolean
	 */
	public function update( $data , $table , $where = '' ) 
	{
		if($table == '' or $where == '') 
		{
			return false;
		}

		$where = ' WHERE '.$where;
		$field = '';
		if( is_string( $data ) && $data != '' ) 
		{
			$field = $data;
		} 
		elseif ( is_array( $data ) && count( $data ) > 0 ) 
		{
			$fields = array();
			foreach( $data as $k => $v ) 
			{
				switch ( substr( $v, 0, 2 ) ) 
				{
					case '+=':
						$v = substr( $v , 2 );
						if ( is_numeric( $v ) ) 
						{
							$fields[] = $this->addSpecialChar($k) . '=' . $this->addSpecialChar( $k ) . '+' . $this->escapeString( $v , '' , false );
						} 
						else 
						{
							continue;
						}
						
						break;
					case '-=':
						$v = substr( $v , 2 );
						if ( is_numeric( $v ) ) 
						{
							$fields[] = $this->addSpecialChar( $k ) . '=' . $this->addSpecialChar( $k ) . '-' . $this->escapeString( $v , '' , false );
						} 
						else 
						{
							continue;
						}
						break;
					default:
						$fields[] = $this->addSpecialChar( $k ) . '=' . $this->escapeString( $v );
				}
			}
			$field = implode( ',' , $fields);
		} 
		else 
		{
			return false;
		}

		$sql = 'UPDATE `' . $this->dbconfig['dbname'] . '` . `' . $table . '` SET ' . $field . $where;
		return $this->query( $sql );
	}
	
	/**
	 * 执行删除记录操作
	 * @param $table 		数据表
	 * @param $where 		删除数据条件,不充许为空。
	 * 						如果要清空表，使用empty方法
	 * @return boolean
	 */
	public function delete( $table, $where ) 
	{
		if ( $table == '' || $where == '' ) 
		{
			return false;
		}
		$where = ' WHERE ' . $where;
		$sql = 'DELETE FROM `' . $this->dbconfig['dbname'] . '` . `' . $table . '`' . $where;
		return $this->query($sql);
	}
	
	/**
	 * 获取最后数据库操作影响到的条数
	 * @return int
	 */
	public function affectedRows() 
	{
		return mysql_affected_rows( $this->link );
	}
	
	/**
	 * 获取数据表主键
	 * @param $table 	数据表
	 * @return array
	 */
	public function getPrimary($table) 
	{
		$this->query( "SHOW COLUMNS FROM $table" );
		while( $r = $this->fetchNext() ) 
		{
			if($r['Key'] == 'PRI') break;
		}
		return $r['Field'];
	}

	/**
	 * 获取表字段
	 * @param $table 	数据表
	 * @return array
	 */
	public function getFields( $table ) 
	{
		$fields = array();
		$this->query( "SHOW COLUMNS FROM $table" );
		while( $r = $this->fetchNext() ) 
		{
			$fields[$r['Field']] = $r['Type'];
		}
		return $fields;
	}

	/**
	 * 检查不存在的字段
	 * @param $table 表名
	 * @return array
	 */
	public function checkFields( $table , $array ) 
	{
		$fields = $this->get_fields( $table );
		$nofields = array();
		foreach( $array as $v ) 
		{
			if( !array_key_exists( $v, $fields ) ) 
			{
				$nofields[] = $v;
			}
		}
		return $nofields;
	}

	/**
	 * 检查表是否存在
	 * @param $table 表名
	 * @return boolean
	 */
	public function isTableExists( $table ) 
	{
		$tables = $this->listTables();
		return in_array( $table, $tables ) ? 1 : 0;
	}
	
	public function listTables() 
	{
		$tables = array();
		$this->query( "SHOW TABLES" );
		while($r = $this->fetchNext()) 
		{
			$tables[] = $r['Tables_in_' . $this->dbconfig['dbname']];
		}
		return $tables;
	}

	/**
	 * 检查字段是否存在
	 * @param $table 表名
	 * @return boolean
	 */
	public function isFieldExists( $table , $field ) 
	{
		$fields = $this->getFields( $table );
		return array_key_exists( $field , $fields );
	}
	
	/**
	 * 返回结果条数
	 * @param string $sql
	 */
	public function numRows( $sql ) 
	{
		$this->lastqueryid = $this->query( $sql );
		return mysql_num_rows( $this->lastqueryid );
	}
	
	/**
	 * 取得结果集中字段数目
	 * @param int $sql
	 */
	public function numFields( $sql ) 
	{
		$this->lastqueryid = $this->query( $sql );
		return mysql_num_fields( $this->lastqueryid );
	}
	
	/**
	 * 取得结果数据
	 * @param string $sql
	 * @param int $row
	 */
	public function result( $sql , $row ) 
	{
		$this->lastqueryid = $this->query( $sql );
		return @mysql_result( $this->lastqueryid, $row );
	}
	
	/**
	 * 获取单表信息
	 * @param string $table
	 */
	public function showTableStatus( $tableName , $onlyOwnDb = true )
	{
		$tables = array();
		$this->query( "SHOW TABLE STATUS FROM `" . $tableName . "`" );
		while($r = $this->fetchNext()) 
		{
			if ( $onlyOwnDb && strpos( $r['Name'] , $this->dbconfig['pre'] ) === 0 )
			{
				$tables[] = $r;
			}
		}
		return $tables;
	}
	
	/**
	 * 关闭连接资源
	 */
	public function close() 
	{
		if ( is_resource( $this->link ) ) 
		{
			@mysql_close( $this->link );
		}
	}
	
	/**
	 * 对字段两边加反引号，以保证数据库安全
	 * @param $value 数组值
	 */
	public function addSpecialChar( &$value ) 
	{
		if( '*' == $value || false !== strpos( $value , '(' ) || false !== strpos( $value , '.' ) || false !== strpos ( $value , '`' ) ) 
		{
			
		} 
		else 
		{
			$value = '`' . trim( $value ) . '`';
		}
		return $value;
	}
	
	/**
	 * 对字段值两边加引号，以保证数据库安全
	 * @param array $value 数组值
	 * @param string $key 
	 * @param bool $quotation 
	 */
	public function escapeString( &$value , $key = '' , $quotation = true ) 
	{
		if ( $quotation ) 
		{
			$q = '\'';
		} 
		else 
		{
			$q = '';
		}
		$value = $q . $value . $q;
		return $value;
	}
	
	/**
	 * 输出错误信息
	 * @param string $message
	 * @param string $sql
	 */
	public function halt( $message = '' , $sql = '' ) 
	{
		$this->errormsg = "<b>MySQL Query : </b> $sql <br /><b> MySQL Error : </b>" . $this->error()." <br /> <b>MySQL Errno : </b>" . $this->errno()." <br /><b> Message : </b> $message <br />";
		$msg = $this->errormsg;
		echo '<div style="font-size:12px;text-align:left; border:1px solid #9cc9e0; padding:1px 4px;color:#000000;font-family:Arial, Helvetica,sans-serif;"><span>' . $msg . '</span></div>';
		exit;
	}
	
	/**
	 * 数据库错误提示
	 */
	public function error() 
	{
		return @mysql_error( $this->link );
	}
	
	/**
	 * 数据库错误代码
	 */
	public function errno() 
	{
		return intval( @mysql_errno( $this->link ) ) ;
	}
	
	/**
	 * 获取mysql版本
	 */
	public function version() 
	{
		if ( !is_resource( $this->link ) )
		{
			$this->connect();
		}
		return mysql_get_server_info( $this->link );
	}
	
}